<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*, java.util.*" %>
<%@ taglib uri="http://struts.apache.org/tags-html" prefix="html" %> 
<%@ taglib uri="http://struts.apache.org/tags-bean" prefix="bean"%>

<html>
<head>
<title>Registration Confirmation Page</title>
</head>

<CENTER>
<h1> Graduate School Application </h1>
<h2> Registration Confirmation Page </h2>
</CENTER>

<body>


<bean:define id="uName" name="appRegForm" property="username" />
<bean:define id="pwd" name="appRegForm" property="pwd" />
<bean:define id="email" name="appRegForm" property="email" />
		
<br><b>You have successfully registered. 
Your login information is as follows: <br></b>
<br>Username: <bean:write name="appRegForm" property="username" />
<br>Password: <bean:write name="appRegForm" property="pwd" />
<br>Email: <bean:write name="appRegForm" property="email" /> <br>

<br><b>You may proceed to start an application.</b>

<form method="GET" action="login/index.jsp">
<br><input type="submit" value="Start application"/>
</form>


<% 
  
	String url = "jdbc:postgresql://localhost/Applications";
	Connection conn = null;
	Statement stmt = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	String instruc = null;
	int userid = 0;
	
	try {
		// Registering Postgresql JDBC driver
		Class.forName("org.postgresql.Driver");
		// Open a connection to the database	
		conn = DriverManager.getConnection(
			"jdbc:postgresql://localhost:5432/Applications", "postgres",
			"postgres");
		// Create the statement
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    				ResultSet.CONCUR_UPDATABLE);

		// store User info into database
		instruc = "INSERT INTO users(user_id, user_name, password, Email)" +
		          "VALUES(?,?,md5(?),?)";

		//find the last id # and +1
		rs = stmt.executeQuery("SELECT user_id FROM users");
		userid = 0;
		while(rs.next())
		{
			if(userid < rs.getInt("user_id") )
	        userid = rs.getInt("user_id");
		}
		userid++;

		ps = conn.prepareStatement( instruc );
		ps.setInt(1, userid);
		ps.setString( 2, uName.toString() );
		ps.setString( 3, pwd.toString() );
		ps.setString( 4, email.toString() );
		ps.executeUpdate();
		
		// store user_roles info into database
		instruc = "INSERT INTO user_roles(user_ref, user_name, role)" +
        "VALUES(?,?,?)";
		ps = conn.prepareStatement( instruc );
		ps.setInt(1,userid);
		ps.setString( 2, uName.toString() );
		ps.setString( 3, "student");
		ps.executeUpdate();
		
		
		rs = stmt.executeQuery("SELECT * FROM users");

		%>
		
		      <table border="1">
            <tr>
                <th>ID</th>
                <th>UserName</th>
                <th>Password</th>
                <th>Email</th>

            </tr>
        <%
            // Iterate over the ResultSet
            while (rs.next()) {
        %>

        <tr>
            <%-- Get the id --%>
            <td>
                <%=rs.getInt("user_id")%>
            </td>

            <%-- Get the pid --%>
            <td>
                <%=rs.getString("user_name")%>
            </td>

            <%-- Get the first name --%>
            <td>
                <%=rs.getString("password")%>
            </td>

    		<%-- Get the first name --%>
            <td>
                <%=rs.getString("Email")%>
            </td>
        	</tr>
       <%
			}
		
	} catch (SQLException e) 
	  { 
		System.out.println(e);
	  }
   
	// close connection
    rs.close();
    stmt.close();
    ps.close();
    conn.close();
    
    %>
 


</body>
</html>